package com.yeziji.devops.sql.builder;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjUtil;
import cn.hutool.core.util.StrUtil;
import com.yeziji.devops.common.SqlBuilder;
import com.yeziji.devops.constant.SqlExecuteTypeEnum;
import com.yeziji.devops.constant.SqlForeignRelationEnum;
import com.yeziji.devops.constant.mysql.MysqlKeywordEnum;
import com.yeziji.devops.constant.mysql.MysqlMemoryEngineEnum;
import com.yeziji.devops.constant.mysql.MysqlRowFormatEnum;
import com.yeziji.devops.sql.base.SqlBuilderBase;
import com.yeziji.devops.sql.constructor.CreateSqlConstructor;
import com.yeziji.devops.sql.info.CreateTableInfo;
import com.yeziji.devops.sql.info.ForeignKeyInfo;
import com.yeziji.devops.sql.info.IndexKeyInfo;

import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * 构建 create 语句
 *
 * <p>该类只负责构建 sql 语句，但是不负责做校验。构建的语句更多是交由用户去自定义，如果最后 sql 执行失败，那么大概率是用户没有用对类型。那么此时最好用户使用自定义的 sql
 * 或者根据异常信息进行类型检查
 *
 * @author gzkemays
 * @since 2023/01/13 12:23 PM
 */
public class CreateBuilder extends SqlBuilderBase<CreateSqlConstructor> implements SqlBuilder {
    public CreateBuilder(CreateSqlConstructor constructor) {
        super(constructor);
    }

    @Override
    public String execute() {
        CreateSqlConstructor createSqlConstructor = super.constructor;
        String name = createSqlConstructor.getName();
        boolean hasAutoIncrement = false;
        if (createSqlConstructor.isExistsDrop() &&
                MysqlKeywordEnum.keywordEquals(createSqlConstructor.getCreate(), MysqlKeywordEnum.TABLE)) {
            // 如果是 table create 判断构建 drop table if exists xxx
            sqlJoiner.add(MysqlKeywordEnum.DROP.getValue())
                    .add(MysqlKeywordEnum.TABLE.getValue())
                    .add(MysqlKeywordEnum.IF.getValue())
                    .add(MysqlKeywordEnum.EXISTS.getValue())
                    .add(name + MysqlKeywordEnum.END.getValue())
                    .add("\n");
        }
        if (MysqlKeywordEnum.keywordEquals(createSqlConstructor.getCreate(), MysqlKeywordEnum.TABLE)) {
            // 创建 table 构建 CREATE TABLE `xxx` (
            sqlJoiner.add(SqlExecuteTypeEnum.CREATE.getValue())
                    .add(MysqlKeywordEnum.TABLE.getValue())
                    .add(MysqlKeywordEnum.quoteVal(name))
                    .add("( \n");
            List<CreateTableInfo> fields = createSqlConstructor.getFields();
            int index = 1;
            for (CreateTableInfo field : fields) {
                // 构建 `xx` varchar(xx)
                sqlJoiner.add(MysqlKeywordEnum.quoteVal(field.getName()));
                if (field.getSize() == 0) {
                    sqlJoiner.add(field.getType());
                } else {
                    sqlJoiner.add(field.getType() + MysqlKeywordEnum.bracket(field.getSize()));
                }
                // 构建编码 character set xxx collate xxx
                if (MysqlKeywordEnum.isStrCharacter(field.getType())) {
                    // 如果是字符串类型追加编码以及排序方式
                    if (StrUtil.isNotBlank(field.getCharacter())) {
                        // 追加 character set xxx
                        sqlJoiner.add(MysqlKeywordEnum.CHARACTER.getValue())
                                .add(MysqlKeywordEnum.SET.getValue())
                                .add(field.getCharacter());
                    }
                    if (StrUtil.isNotBlank(field.getCollate())) {
                        // 追加排序方式 collate xxx
                        sqlJoiner.add(MysqlKeywordEnum.COLLATE.getValue()).add(field.getCollate());
                    }
                }
                // 追加是否允许 NULL
                sqlJoiner.add(field.isAcceptNull() ? MysqlKeywordEnum.NULL.getValue() : MysqlKeywordEnum.NOT_NULL.getValue());
                // 追加默认值, extra 额外值进行 二选一，其中额外值优先级最大，如果是自增那么自增优先级最大
                if (field.isAutoIncrement()) {
                    hasAutoIncrement = true;
                    sqlJoiner.add(MysqlKeywordEnum.AUTO_INCREMENT.getValue());
                } else {
                    if (StrUtil.isNotBlank(field.getExtra())) {
                        // 设置自增
                        sqlJoiner.add(MysqlKeywordEnum.DEFAULT.getValue()).add(field.getExtra());
                    } else if (ObjUtil.isNotNull(field.getDefaultValue())) {
                        // 设置默认值
                        sqlJoiner.add(MysqlKeywordEnum.DEFAULT.getValue())
                                .add(buildTypeValue(field.getDefaultValue()));
                    } else if (field.isAcceptNull()) {
                        // 如果支持 null，则默认值设置为 null。否则不设置默认值
                        sqlJoiner.add(MysqlKeywordEnum.DEFAULT.getValue()).add(MysqlKeywordEnum.NULL.getValue());
                    } else if (field.isCurrentTimestamp()) {
                        // 默认值为当前时间戳
                        sqlJoiner.add(MysqlKeywordEnum.DEFAULT.getValue())
                                .add(MysqlKeywordEnum.CURRENT_TIMESTAMP.getValue());
                    }
                }
                if (StrUtil.isNotBlank(field.getComment())) {
                    // 追加 comment
                    sqlJoiner.add(MysqlKeywordEnum.COMMENT.getValue())
                            .add(MysqlKeywordEnum.singeQuoteVal(field.getComment()));
                }
                if (index != fields.size()) {
                    sqlJoiner.add(", \n");
                }
                index++;
            }
            // 构建主键
            List<CreateTableInfo> primaryKeys = fields.stream().filter(CreateTableInfo::isPrimary).collect(Collectors.toList());
            if (CollectionUtil.isNotEmpty(primaryKeys)) {
                // 需要继续构建才进行换行
                sqlJoiner.add(", \n");
                // 构建 primary key
                sqlJoiner.add(MysqlKeywordEnum.PRIMARY.getValue()).add(MysqlKeywordEnum.KEY.getValue());
                // 构建 (`id`, `xx` ...) 允许联合主键
                sqlJoiner.add(
                        MysqlKeywordEnum.brackets(
                                primaryKeys.stream().map(CreateTableInfo::getName).collect(Collectors.toList()),
                                true)
                );
                // using btree
                sqlJoiner.add(MysqlKeywordEnum.USING.getValue()).add(MysqlKeywordEnum.BTREE.getValue());
            }
            // 构建索引
            List<IndexKeyInfo> indexKeys = createSqlConstructor.getIndexKeys();
            if (CollectionUtil.isNotEmpty(indexKeys)) {
                // 需要继续构建才进行换行
                sqlJoiner.add(", \n");
                // 构建 INDEX
                int i = 1;
                for (IndexKeyInfo indexKey : indexKeys) {
                    // 构建 INDEX a (`a`,`b`,`c`..) USING BTREE，如果 BTREE 和 HASH 没有，则默认为 BTREE
                    if (indexKey.getIndexMethod().equals(MysqlKeywordEnum.FULL_TEXT.getValue())) {
                        sqlJoiner.add(MysqlKeywordEnum.INDEX.getValue())
                                .add(MysqlKeywordEnum.quoteVal(indexKey.getName()))
                                .add(MysqlKeywordEnum.brackets(indexKey.getFields(), true))
                                .add(MysqlKeywordEnum.USING.getValue())
                                .add(Optional.ofNullable(MysqlKeywordEnum.getByValue(indexKey.getIndexMethod()))
                                        .orElse(MysqlKeywordEnum.BTREE)
                                        .getValue()
                                );
                    } else {
                        // TODO：排查 FULL_TEXT 构建是否异常
                    }
                    if (i != indexKeys.size()) {
                        sqlJoiner.add(", \n");
                    }
                }
            }
            // 构建外键
            List<ForeignKeyInfo> foreignKeys = createSqlConstructor.getForeignKeys();
            if (CollectionUtil.isNotEmpty(foreignKeys)) {
                // 需要继续构建才进行换行
                sqlJoiner.add(", \n");
                int i = 1;
                for (ForeignKeyInfo foreignKey : foreignKeys) {
                    sqlJoiner
                            // CONSTRAINT `name` FOREIGN KEY
                            .add(MysqlKeywordEnum.CONSTRAINT.getValue())
                            .add(MysqlKeywordEnum.quoteVal(foreignKey.getName()))
                            .add(MysqlKeywordEnum.FOREIGN_KEY.getValue())
                            // (`a`,`b`,`c` ...)
                            .add(MysqlKeywordEnum.brackets(foreignKey.getForeignKeyFields(), true))
                            // REFERENCES `referencesTable`
                            .add(MysqlKeywordEnum.REFERENCES.getValue())
                            .add(MysqlKeywordEnum.quoteVal(foreignKey.getReferencesTable()))
                            // (`e`,`f`,`g` ...)
                            .add(MysqlKeywordEnum.brackets(foreignKey.getReferencesFields(), true))
                            // ON DELETE xxx ON UPDATE xxx，默认取 RESTRICT
                            .add(MysqlKeywordEnum.ON.getValue())
                            .add(MysqlKeywordEnum.DELETE.getValue())
                            .add(Optional.ofNullable(SqlForeignRelationEnum.getByValue(foreignKey.getDeleteMethod()))
                                    .orElse(SqlForeignRelationEnum.RESTRICT)
                                    .getValue())
                            .add(MysqlKeywordEnum.ON.getValue())
                            .add(MysqlKeywordEnum.UPDATE.getValue())
                            .add(Optional.ofNullable(
                                            SqlForeignRelationEnum.getByValue(foreignKey.getUpdateMethod()))
                                    .orElse(SqlForeignRelationEnum.RESTRICT)
                                    .getValue());
                    if (i != foreignKeys.size()) {
                        sqlJoiner.add(", \n");
                    }
                }
            }
            sqlJoiner.add("\n");
            sqlJoiner.add(")");
            sqlJoiner.add(MysqlKeywordEnum.ENGINE.getValue())
                    .add("=")
                    // 默认采取 innodb
                    .add(Optional.ofNullable(
                                    MysqlMemoryEngineEnum.getByValue(createSqlConstructor.getEngine()))
                            .orElse(MysqlMemoryEngineEnum.INNODB)
                            .getValue());
            if (hasAutoIncrement) {
                sqlJoiner.add(MysqlKeywordEnum.AUTO_INCREMENT.getValue()).add("=").add("1");
            }
            sqlJoiner
                    // 默认使用 utf8 字符集
                    .add(MysqlKeywordEnum.CHARACTER.getValue())
                    .add(MysqlKeywordEnum.SET.getValue())
                    .add("=")
                    .add(Optional.ofNullable(createSqlConstructor.getCharacter()).orElse("utf8"))
                    // 默认使用 utf8_general_ci 排序
                    .add(MysqlKeywordEnum.COLLATE.getValue())
                    .add("=")
                    .add(Optional.ofNullable(createSqlConstructor.getCollate()).orElse("utf8_general_ci"));
            if (StrUtil.isNotBlank(createSqlConstructor.getComment())) {
                sqlJoiner.add(MysqlKeywordEnum.COMMENT.getValue())
                        .add("=")
                        .add(MysqlKeywordEnum.singeQuoteVal(createSqlConstructor.getComment()));
            }
            sqlJoiner.add(MysqlKeywordEnum.ROW_FORMAT.getValue())
                    .add("=")
                    .add(Optional.ofNullable(
                                    MysqlRowFormatEnum.getByValue(createSqlConstructor.getRowFormat()))
                            .orElse(MysqlRowFormatEnum.DYNAMIC)
                            .getValue());
        } else if (MysqlKeywordEnum.keywordEquals(createSqlConstructor.getCreate(), MysqlKeywordEnum.DATABASE)) {
            // 创建 table 构建 CREATE TABLE `xxx` (
            sqlJoiner.add(SqlExecuteTypeEnum.CREATE.getValue())
                    .add(MysqlKeywordEnum.DATABASE.getValue())
                    .add(MysqlKeywordEnum.quoteVal(name));
        }
        return sqlJoiner.toString();
    }

    private String buildTypeValue(Object value) {
        if (value instanceof String) {
            return "\"" + value + "\"";
        }
        return String.valueOf(value);
    }
}
